﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using MyATM.Entity;

namespace MyATM.DataAccess
{
    /// <summary>
    /// This Class provided methods to access data in Entities.
    /// </summary>
    public class AccountDAO
    {
        /// <summary>
        /// Add a new card
        /// </summary>
        /// <param name="atmCard">ATM Card Object.</param>
        /// <returns></returns>
        public int AddNewCard(Card atmCard)
        {
            int result;
            string cmd =
                string.Format(
                    "INSERT INTO DebitCard ( CardNumber, CardPIN, AccountNumber, OpenedDate, EndDate, Status ) SELECT \"{0}\" AS Expr1, \"{1}\" AS Expr2, \"{2}\" AS Expr3, \"{3}\" AS Expr4, \"{4}\" AS Expr5, {5} AS Expr6;",
                    atmCard.CardId, atmCard.CardPin, atmCard.AccountId, atmCard.CreateDate, atmCard.EndDate,
                    atmCard.Status);
            result = SqlAccess.ExcuteNonQuery(cmd);
            return result;
        }
        /// <summary>
        /// Chinh sua thong tin status cua the.
        /// </summary>
        /// <param name="cardId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int ModifyCard(string cardId, int status)
        {
            int result;
            string cmd = string.Format("UPDATE DebitCard SET DebitCard.Status = {0} WHERE (((DebitCard.CardNumber)=\"{1}\"));", status, cardId);
            result = SqlAccess.ExcuteNonQuery(cmd);
            return result;
        }
        /// <summary>
        /// Lay ve so tien toi da co the rut cua card
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public double GetOverdraftLimit(string cardId)
        {
            double result = 0;
            string cmd = string.Format(
                    "SELECT Account.OD FROM Account INNER JOIN DebitCard ON Account.AccountNumber = DebitCard.AccountNumber WHERE  ((DebitCard.CardNumber)=\"{0}\");",
                    cardId);
            result = Convert.ToInt32(SqlAccess.ExcuteQuery(cmd).Rows[0][0].ToString());
            return result;
        }
        /// <summary>
        /// Xem so du tai khoan
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public double ViewBalance(string cardId)
        {
            double result = 0;
            string cmd = string.Format("SELECT Account.Balance FROM Account " +
                                       "INNER JOIN DebitCard ON Account.AccountNumber = DebitCard.AccountNumber " +
                                       "WHERE DebitCard.CardNumber = \"{0}\";", cardId);
            DataTable dt = new DataTable();
            dt = SqlAccess.ExcuteQuery(cmd);
            if(dt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                return result = Convert.ToDouble(dt.Rows[0][0].ToString());
            }
        }
        /// <summary>
        /// Kiem tra account co bi lock hay chua
        /// </summary>
        /// <param name="accountNumber">AccountNumber</param>
        /// <returns>true or false</returns>
        public bool CheckAccountIsLocked(string accountNumber)
        {
            int status;
            string cmd = string.Format("SELECT Account.Status FROM Account WHERE (((Account.AccountNumber)=\"{0}\"))",
                                       accountNumber);
            status = Convert.ToInt32(SqlAccess.ExcuteQuery(cmd).Rows[0][0].ToString());
            if (status == 1)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// Lay ve thong tin Account cua mot tai khoan the.
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public Account GetAccount(string cardId)
        {
            Account account = new Account();
            string cmd =
                string.Format(
                    "SELECT Account.AccountNumber, Account.Balance FROM Account INNER JOIN DebitCard ON Account.AccountNumber = DebitCard.AccountNumber WHERE (((DebitCard.CardNumber)=\"{0}\"));",
                    cardId);
            DataRow dataRow = SqlAccess.ExcuteQuery(cmd).Rows[0];
            account.AccountId = dataRow[0].ToString();
            account.Balance = Convert.ToDouble(dataRow[1].ToString());
            return account;
        }
        /// <summary>
        /// Kiem tra account co valid hay khong
        /// </summary>
        /// <param name="accId">ID cua account</param>
        /// <returns>true or false</returns>
        public bool ValidAccountId(string accId)
        {
            int result;
            string cmd = string.Format("SELECT TOP 1 FROM Account WHERE Account.AccountNumber = \"{0}\"", accId);
            result = SqlAccess.ExcuteQuery(cmd).Rows.Count;
            if(result == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// Kiem tra Id card co hop le
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public bool ValidCardId(string cardId)
        {
            int result;
            string cmd = string.Format("SELECT 1 FROM DebitCard WHERE DebitCard.CardNumber = \"{0}\"", cardId);
            result = SqlAccess.ExcuteQuery(cmd).Rows.Count;
            if (result == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
